# -*- coding: utf-8 -*-
import xlrd,sqlite3,xlwt,datetime
from pytz import UTC

def getDate(date):
    __startDt = datetime.date(1899, 12, 31).toordinal() - 1
    date_f = 0.0
    if type(date)<>float:
        try:
            date_f = float(date)
        except:
            date_f = None
    else:
        date_f=date

    if type(date_f)==float:
        return from_ordinalf(__startDt+date_f).strftime("%Y-%m-%d %H:%M:%S")

def from_ordinalf(x, tz=None):
    """
    convert Gregorian float of the date, preserving hours, minutes,
    seconds and microseconds. return value is a datetime
    """
    if tz is None: tz = UTC
    ix = int(x)
    dt = datetime.datetime.fromordinal(ix)
    remainder = float(x) - ix
    hour, remainder = divmod(24*remainder, 1)
    minute, remainder = divmod(60*remainder, 1)
    second, remainder = divmod(60*remainder, 1)
    microsecond = int(1e6*remainder)
    if microsecond<10: microsecond=0 # compensate for rounding errors
    dt = datetime.datetime(
        dt.year, dt.month, dt.day, int(hour), int(minute), int(second),
        microsecond, tzinfo=UTC).astimezone(tz)

    if microsecond>999990: # compensate for rounding errors
        dt += datetime.timedelta(microseconds=1e6-microsecond)
    return dt


def getIdFromDict(d,v):
    for x in d:
        if x['VAL']==v:
            return x['ID']

def getCommunityId(c,a):
    id=conn.execute("select CommID from tComm where Community=? and AccessType=?",(c,a)).fetchone()
    if id:
        id=id[0]
    conn.execute("replace into tComm(CommID,Community,AccessType,InstallDate) values(?,?,?,?)",(id,c,a,today))
    return conn.execute("select last_insert_rowid()").fetchone()[0]



def getStationId(row):
#    print s
    id=conn.execute("select StatID from tStat where StationCode=? and RoomType=? and Location=? and Community=?",(row[u'StationCode'],row[u'RoomType'],row[u'Location'],row[u'Community'])).fetchone()
    if id:
        id=id[0]
    conn.execute("replace into tStat(StatID,StationCode,RoomType,Location,Community) values(?,?,?,?,?)",(id,row[u'StationCode'],row[u'RoomType'],row[u'Location'],row[u'Community']))
    return conn.execute("select last_insert_rowid()").fetchone()[0]

def getSignalId(s):
    if s[0]=='P':
        return getIdFromDict(SIGN,'IP')
    if s[0]=='C':
        return getIdFromDict(SIGN,'CATV')

        
def getSplitterId(row):
    id=conn.execute("select SpltID from tSplt where SplitterCode=? and SplitterType=? and SignalType=? and Room=? and InstallAddress=?",(row[u'SplitterCode'],row[u'SplitterType'],row[u'SignalType'],row[u'Room'],row[u'InstallAddress'])).fetchone()
    if id:
        print id
        id=id[0]
    conn.execute("replace into tSplt(SpltID,SplitterCode,SplitterType,SignalType,Room,InstallAddress,PONPort) values(?,?,?,?,?,?,?)",(id,row[u'SplitterCode'],row[u'SplitterType'],row[u'SignalType'],row[u'Room'],row[u'InstallAddress'],row[u'PONPort']))
    return conn.execute("select last_insert_rowid()").fetchone()[0]

def readODN(accesstype):
    odn=readSheet('odn')
    comm=[]
    splt=[]
    stat=[]
    for row in odn:
        row[u'AccessType']=getIdFromDict(ACCS,accesstype)
        row[u'Community']=getCommunityId(row[u'小区名称'],row[u'AccessType'])
        row[u'Room']=getIdFromDict(ROOM,row[u'分前端'])
        row[u'RoomType']=getIdFromDict(RMTP,row[u'机房类型'])
        row[u'SplitterType']=getIdFromDict(SPLT,'%s-%s'%(row[u'分光器'],row[u'分光器类型']))
        row[u'SignalType']=getSignalId(row[u'分光器编号'])
        row[u'StationCode']=row[u'机房编号']
        row[u'Location']=row[u'机房地址']
        row[u'PONPort']=row[u'PON']
        row[u'SplitterCode']=row[u'分光器编号']
        row[u'StatID']=getStationId(row)
        row[u'InstallAddress']=row[u'StatID']
        row[u'SpltID']=getSplitterId(row)
        comm.append((row[u'Community'],row[u'小区名称'],row[u'AccessType']))
        stat.append((row[u'StatID'],row[u'机房编号'],row[u'RoomType'],row[u'机房地址'],row[u'Community']))
        splt.append((row[u'SpltID'],row[u'分光器编号'],row[u'SplitterType'],row[u'SignalType'],row[u'Room'],row[u'StatID'],row[u'PON'],row[u'分前端'],'%s-%s'%(row[u'分光器'],row[u'分光器类型'])))
    return list(set(comm)),list(set(stat)),list(set(splt))


def cleanRow(row):
    for i,e in enumerate(row):
        if type(e)==str:
            row[i]=e.strip()
            if e=='':
                row[i]=None
    return row

def cleanCell(cell):
    if type(cell)==str:
        cell=cell.strip()
        if cell=='':
            cell=None
    return cell

def readSheet(sheet):
    sh = wb.sheet_by_name(sheet)
    nrows = sh.nrows
    ncols = sh.ncols
    headers=dict((i,sh.cell_value(0,i)) for i in range(ncols))
    return [dict( (headers[j], cleanCell(sh.cell_value(i, j))) for j in headers ) for i in range(1, nrows)]


def cellUpper(cell):
    if cell:
        return cell.upper()

def cellInt(cell):
    if cell and type(cell)==float:
        return int(cell)


def getResId(row):

    id=conn.execute("select ResID from tRes where Community=? and InstallAddress=? and Device=? and Station=?",(row[u'Community'],row[u'InstallAddress'],row[u'Device'],row[u'Station'])).fetchone()
    if id:
        id=id[0]
    conn.execute("replace into tRes(ResID,IP,MAC,InstallAddress,IPTVVlan,OpticalPower,ManageVlan,SubnetMask,Gateway,AssetNumber,SN,Memo,CMCCVlan,Device,OSPort,OutLevel,RSSI,ParentRes,Splitter,InstallDate,InstallPerson,PowerFrom,Station,Community,OutLevel1,OutLevel2,OutLevel3,OutLevel4) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",(id,row[u'IP'],row[u'MAC'],row[u'InstallAddress'],row[u'IPTVVlan'],row[u'OpticalPower'],row[u'ManageVlan'],row[u'SubnetMask'],row[u'Gateway'],row[u'AssetNumber'],row[u'SN'],row[u'Memo'],row[u'CMCCVlan'],row[u'Device'],row[u'OSPort'],row[u'OutLevel'],row[u'RSSI'],row[u'ParentRes'],row[u'Splitter'],row[u'InstallDate'],row[u'InstallPerson'],row[u'PowerFrom'],row[u'Station'],row[u'Community'],row[u'OutLevel1'],row[u'OutLevel2'],row[u'OutLevel3'],row[u'OutLevel4']))
    return conn.execute("select last_insert_rowid()").fetchone()[0]

def getNameFromCommunity(communtiy,id):
    for c in communtiy:
        if c[0]==id:
            return c[1]

def getLocationFromStation(station,id):
    for s in station:
        if id==s[0]:
            return s[1]

def getRoomFromSplitter(splitter,id):
    for s in splitter:
        if s[0]==id:
            return s[7]

def getSplitterFromSplitter(splitter,id):
    for s in splitter:
        if s[0]==id:
            return s[8]

def getPonFromSplitter(splitter,id):
    for s in splitter:
        if s[0]==id:
            return s[6]

def getAddressFromStation(station,addr):
    if addr[0]=='S':
        for s in station:
            if s[1]==addr:
                return s[3]
    else:
        return addr

def getIdFromStation(station,stat):
    for s in station:
        if s[1]==stat:
            return s[0]

def getIdFromSplitter(splitter,splt):
    for s in splitter:
        if s[1]==splt:
            return s[0]

def getIdFromCommunity(community,comm):
    for c in community:
        if c[2]==comm:
            return c[0]
            
def readCATV(community,station,splitter):
    catv=readSheet('catv')
    res=[]
    for row in catv:
        row[u'IP']=None
        row[u'MAC']=None
        row[u'InstallAddress']=getAddressFromStation(station,row[u'安装地址'])
        row[u'IPTVVlan']=None
        row[u'OpticalPower']=row[u'光功率']
        row[u'ManageVlan']=None
        row[u'SubnetMask']=None
        row[u'Gateway']=None
        row[u'AssetNumber']=cellUpper(row[u'资产编号'])
        row[u'SN']=cellUpper(row[u'SN'])
        row[u'Memo']=row[u'备注']
        row[u'CMCCVlan']=None
        row[u'Device']=getIdFromDict(DEVC,'%s-%s'%(row[u'设备型号'],row[u'设备类型']))
        row[u'OSPort']=None
        row[u'OutLevel']=None
        row[u'RSSI']=None
        row[u'ParentRes']=None
        row[u'Splitter']=getIdFromSplitter(splitter,row[u'分光器编号'])
        row[u'InstallDate']=getDate(row[u'安装日期'])
        row[u'InstallPerson']=getIdFromDict(EMPE,row[u'安装人员'])
        row[u'PowerFrom']=row[u'电源接入']
        row[u'Station']=getIdFromStation(station,row[u'机房编号'])
        row[u'Community']=getIdFromCommunity(community,getIdFromDict(ACCS,'CATV'))
        row[u'OutLevel1']=row[u'端口1电平']
        row[u'OutLevel2']=row[u'端口2电平']
        row[u'OutLevel3']=row[u'端口3电平']
        row[u'OutLevel4']=row[u'端口4电平']
        row[u'ResID']=getResId(row)
        res.append(row)
    return res

def readLAN(community,station,splitter):
    lan=readSheet('lan')
    res=[]
    for row in lan:
        # row[u'IP']=None
        row[u'MAC']=cellUpper(row[u'MAC'])
        row[u'InstallAddress']=getAddressFromStation(station,row[u'安装地址'])
        row[u'IPTVVlan']=row[u'IPTV VLAN']
        row[u'OpticalPower']=row[u'光功率']
        row[u'ManageVlan']=row[u'管理VLAN']
        row[u'SubnetMask']=row[u'子网掩码']
        row[u'Gateway']=row[u'网关']
        row[u'AssetNumber']=cellUpper(row[u'资产编号'])
        row[u'SN']=cellUpper(row[u'SN'])
        row[u'Memo']=row[u'备注']
        row[u'CMCCVlan']=row[u'移动VLAN']
        row[u'Device']=getIdFromDict(DLAN,row[u'设备型号'])
        row[u'OSPort']=None
        row[u'OutLevel']=None
        row[u'RSSI']=None
        row[u'ParentRes']=None
        row[u'Splitter']=getIdFromSplitter(splitter,row[u'分光器编号'])
        row[u'InstallDate']=getDate(row[u'安装日期'])
        row[u'InstallPerson']=getIdFromDict(EMPE,row[u'安装人员'])
        row[u'PowerFrom']=row[u'电源接入']
        row[u'Station']=getIdFromStation(station,row[u'机房编号'])
        row[u'Community']=getIdFromCommunity(community,getIdFromDict(ACCS,'LAN'))
        row[u'OutLevel1']=None
        row[u'OutLevel2']=None
        row[u'OutLevel3']=None
        row[u'OutLevel4']=None
        row[u'ResID']=getResId(row)
        addCoverage(row)
        res.append(row)
    return res

def getAddrId(a):
    id=conn.execute("select AddrID from tAddr where Address=?",(a,)).fetchone()
    if id:
        id=id[0]
    conn.execute("replace into tAddr(AddrID,Address) values(?,?)",(id,a))
    return conn.execute("select last_insert_rowid()").fetchone()[0]
    
def addCoverage(row):
    coverage=row[u'覆盖范围'].split(';')
    for c in coverage:
        conn.execute("replace into rAddrComm(AddrID,CommID) values(?,?)",(getAddrId(c),row[u'Community']))

    
    
def toSpace(cell):
    if cell is None:
        return ''

def exportCATV(community,station,resource):
    wbk=xlwt.Workbook()
    communityname=''
    for res in resource:
        if res[u'设备类型']==u'光站':
            communityname=getNameFromCommunity(community,res[u'Community'])

            fnt = xlwt.Font()
            fnt.name=u'宋体'
            fnt.bold = True
            fnt.height= 20*0x14
            al = xlwt.Alignment()
            al.horz = xlwt.Alignment.HORZ_CENTER
            al.vert = xlwt.Alignment.VERT_CENTER
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al
            sheet = wbk.add_sheet('%s-%s'%(res[u'设备类型'],res[u'InstallAddress']),cell_overwrite_ok=True)
            sheet.write_merge(0,0,0,5,u'宁波数字电视有限公司光站设备安装登记表',style)

            fnt = xlwt.Font()
            fnt.height= 12*0x14
            fnt.name=u'宋体'
            al = xlwt.Alignment()
#            al.horz = xlwt.Alignment.HORZ_CENTER
            al.vert = xlwt.Alignment.VERT_BOTTOM
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al


            sheet.write(1,0,u'编号：',style)
            fnt = xlwt.Font()
            fnt.height= 12*0x14
            fnt.name=u'宋体'
            al = xlwt.Alignment()
            al.horz = xlwt.Alignment.HORZ_RIGHT
            al.vert = xlwt.Alignment.VERT_BOTTOM
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al

            if res[u'InstallDate'] is None:
                sheet.write_merge(1,1,4,5,u'%s 年 %s 月 %s 日'%('','',''),style)
            else:
                installdate=datetime.datetime.strptime(res[u'InstallDate'],'%Y-%m-%d %H:%M:%S')
                sheet.write_merge(1,1,4,5,u'%s 年 %s 月 %s 日'%(installdate.year,installdate.month,installdate.day),style)


            fnt = xlwt.Font()
            fnt.height= 12*0x14
            fnt.name=u'宋体'
            al = xlwt.Alignment()
            al.horz = xlwt.Alignment.HORZ_CENTER
            al.vert = xlwt.Alignment.VERT_CENTER
            brd1 = xlwt.Borders()
            brd1.left = 0x01
            brd1.right = 0x01
            brd1.top = 0x01
            brd1.bottom = 0x01
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al
            style.borders=brd1
            for i in xrange(6):
                sheet.col(i).width=256*16



            sheet.write_merge(2,2,0,1,u'小区名称',style)
            sheet.write_merge(2,2,2,3,communityname,style)

            sheet.write(2,4,u'安装人',style)
            sheet.write(2,5,res[u'安装人员'],style)

            sheet.write_merge(3,3,0,1,u'小区地址',style)
            sheet.write_merge(3,3,2,3,'',style)

            sheet.write(3,4,u'电话',style)
            sheet.write(3,5,'',style)

            sheet.write_merge(4,4,0,1,u'设备型号',style)
            sheet.write_merge(4,4,2,3,res[u'设备型号'],style)

            sheet.write(4,4,u'设备厂家',style)
            sheet.write(4,5,getIdFromDict(MANU,res[u'设备型号']),style)

            sheet.write_merge(5,5,0,1,u'资产编号',style)
            sheet.write_merge(5,5,2,3,res[u'资产编号'],style)

            sheet.write(5,4,u'光功率',style)
            sheet.write(5,5,res[u'光功率'],style)

            sheet.write_merge(6,6,0,1,u'设备安装地址',style)
            sheet.write_merge(6,6,2,5,res[u'InstallAddress'],style)

            sheet.write_merge(7,7,0,1,u'电源接入路由',style)
            sheet.write_merge(7,7,2,5,res[u'电源接入'],style)

            sheet.write_merge(8,8,0,1,u'光站输出端口',style)
            sheet.write(8,2,u'PORT1',style)
            sheet.write(8,3,u'PORT2',style)
            sheet.write(8,4,u'PORT3',style)
            sheet.write(8,5,u'PORT4',style)
            sheet.write_merge(9,14,0,0,u'输出电平dbuv',style)
            sheet.write(9,1,u'120.25Mhz',style)
            sheet.write(10,1,u'224.25Mhz',style)
            sheet.write(11,1,u'304.25Mhz',style)
            sheet.write(12,1,u'448.25Mhz',style)
            sheet.write(13,1,u'527.25Mhz',style)
            sheet.write(14,1,u'743.25Mhz',style)
            for j in xrange(4):
                for i,ol in enumerate(res[u'OutLevel%d'%(j+1)].split(',')):
                    sheet.write(9+i,2+j,ol,style)

            sheet.write_merge(15,25,0,0,u'备注',style)
            sheet.write_merge(15,25,1,5,res[u'备注'],style)


            fnt = xlwt.Font()
            fnt.height = 16*28
            style = xlwt.XFStyle()
            style.font = fnt

            for i in range(1, 26):
                sheet.row(i).set_style(style)


    wbk.save('%s-CATV.xls'%communityname)

def exportLAN(community,station,splitter,resource):
    wbk=xlwt.Workbook()
    communityname=''
    for res in resource:
        if getIdFromDict(DTYP,res[u'设备型号'])==u'ONU':
            communityname=getNameFromCommunity(community,res[u'Community'])

            fnt = xlwt.Font()
            fnt.name=u'宋体'
            fnt.bold = True
            fnt.height= 20*0x14
            al = xlwt.Alignment()
            al.horz = xlwt.Alignment.HORZ_CENTER
            al.vert = xlwt.Alignment.VERT_CENTER
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al
            sheet = wbk.add_sheet('%s-%s'%('ONU',res[u'InstallAddress']),cell_overwrite_ok=True)
            sheet.write_merge(0,0,0,5,u'宁波数字电视有限公司LAN设备安装登记表',style)

            fnt = xlwt.Font()
            fnt.height= 12*0x14
            fnt.name=u'宋体'
            al = xlwt.Alignment()
#            al.horz = xlwt.Alignment.HORZ_CENTER
            al.vert = xlwt.Alignment.VERT_BOTTOM
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al


            sheet.write(1,0,u'编号：',style)
            fnt = xlwt.Font()
            fnt.height= 12*0x14
            fnt.name=u'宋体'
            al = xlwt.Alignment()
            al.horz = xlwt.Alignment.HORZ_RIGHT
            al.vert = xlwt.Alignment.VERT_BOTTOM
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al

            if res[u'InstallDate'] is None:
                sheet.write_merge(1,1,4,5,u'%s 年 %s 月 %s 日'%('','',''),style)
            else:
                installdate=datetime.datetime.strptime(res[u'InstallDate'],'%Y-%m-%d %H:%M:%S')
                
                sheet.write_merge(1,1,4,5,u'%s 年 %s 月 %s 日'%(installdate.year,installdate.month,installdate.day),style)


            fnt = xlwt.Font()
            fnt.height= 12*0x14
            fnt.name=u'宋体'
            al = xlwt.Alignment()
            al.horz = xlwt.Alignment.HORZ_CENTER
            al.vert = xlwt.Alignment.VERT_CENTER
            brd1 = xlwt.Borders()
            brd1.left = 0x01
            brd1.right = 0x01
            brd1.top = 0x01
            brd1.bottom = 0x01
            style = xlwt.XFStyle()
            style.font = fnt
            style.alignment=al
            style.borders=brd1
            for i in xrange(6):
                sheet.col(i).width=256*16



            sheet.write(2,0,u'小区名称',style)
            sheet.write_merge(2,2,1,2,communityname,style)

            sheet.write(2,3,u'安装人',style)
            sheet.write_merge(2,2,4,5,res[u'安装人员'],style)

            sheet.write(3,0,u'小区地址',style)
            sheet.write_merge(3,3,1,2,'',style)

            sheet.write(3,3,u'电话',style)
            sheet.write_merge(3,3,4,5,'',style)

            sheet.write(4,0,u'设备型号',style)
            sheet.write_merge(4,4,1,2,res[u'设备型号'],style)

            sheet.write(4,3,u'设备厂家',style)
            sheet.write_merge(4,4,4,5,getIdFromDict(MANU,res[u'设备型号']),style)

            sheet.write(5,0,u'资产编号',style)
            sheet.write_merge(5,5,1,2,res[u'资产编号'],style)

            sheet.write(5,3,u'光功率',style)
            sheet.write_merge(5,5,4,5,res[u'光功率'],style)

            sheet.write(6,0,u'设备安装地址',style)
            sheet.write_merge(6,6,1,5,res[u'InstallAddress'],style)

            sheet.write(7,0,u'电源接入路由',style)
            sheet.write_merge(7,7,1,5,res[u'电源接入'],style)

            sheet.write(8,0,u'设备覆盖范围',style)
            sheet.write_merge(8,8,1,5,res[u'覆盖范围'],style)

            sheet.write(9,0,u'上联机房',style)
            sheet.write_merge(9,9,1,2,getLocationFromStation(station,res[u'Station']),style)

            sheet.write(9,3,u'分光器',style)
            sheet.write_merge(9,9,4,5,getSplitterFromSplitter(splitter,res[u'Splitter']),style)

            sheet.write(10,0,u'IP地址',style)
            sheet.write_merge(10,10,1,2,res[u'IP'],style)

            sheet.write(10,3,u'MAC',style)
            sheet.write_merge(10,10,4,5,res[u'MAC'],style)

            sheet.write(11,0,u'管理VLAN',style)
            sheet.write_merge(11,11,1,2,res[u'管理VLAN'],style)

            sheet.write(11,3,u'网关',style)
            sheet.write_merge(11,11,4,5,res[u'网关'],style)

            sheet.write(12,0,u'序列号',style)
            sheet.write_merge(12,12,1,2,res[u'SN'],style)

            sheet.write(12,3,u'IPTV VLAN',style)
            sheet.write_merge(12,12,4,5,res[u'IPTV VLAN'],style)

            sheet.write(13,0,u'分前端',style)
            sheet.write_merge(13,13,1,2,getRoomFromSplitter(splitter,res[u'Splitter']),style)

            sheet.write(13,3,u'PON口',style)
            sheet.write_merge(13,13,4,5,getPonFromSplitter(splitter,res[u'Splitter']),style)

            sheet.write_merge(14,24,0,0,u'备注',style)
            sheet.write_merge(14,24,1,5,res[u'备注'],style)


            fnt = xlwt.Font()
            fnt.height = 16*28
            style = xlwt.XFStyle()
            style.font = fnt

            for i in range(1, 26):
                sheet.row(i).set_style(style)


    wbk.save('%s-LAN.xls'%communityname)
    
def readXLS(at):
    # at='LAN'
    # at='CATV'
    # at='EOC'
    print at
    community,station,splitter=readODN(at)
    print community,station,splitter
    if at=='CATV':
        resource=readCATV(community,station,splitter)
        exportCATV(community,station,resource)
    if at=='LAN':
        resource=readLAN(community,station,splitter)
        exportLAN(community,station,splitter,resource)
    if at=='EOC':
        resource=readEOC(community,station,splitter)
        exportEOC(community,station,resource)
        resource=readCATV(community,station,splitter)
        exportCATV(community,station,resource)

today=datetime.datetime.strftime(datetime.datetime.today(),'%Y-%m-%d')
db="res3.db3"
xls="lan.xls"
conn=sqlite3.connect(db)
#conn.text_factory=str
conn.row_factory = sqlite3.Row
ACCS=conn.execute("select AccsTypeID as ID,AccessType as VAL from cAccsType").fetchall()
DEVC=conn.execute("select DevcID as ID,Device||'-'||DeviceType as VAL from vDevice").fetchall()
DLAN=conn.execute("select DevcID as ID,Device as VAL from vDevice").fetchall()
DTYP=conn.execute("select DeviceType as ID,Device as VAL from vDevice").fetchall()
MANU=conn.execute("select DeviceManufacturer as ID,Device as VAL from vDevice").fetchall()
RMTP=conn.execute("select RoomTypeID as ID,RoomType as VAL from cRoomType").fetchall()
SIGN=conn.execute("select SignTypeID as ID,SignalType as VAL from cSignType").fetchall()
SPLT=conn.execute("select SpltTypeID as ID,SubInCount||'*'||SubOutCount||'-'||SetupMode as VAL from cSpltType").fetchall()
EMPE=conn.execute("select EmpeID as ID,FullName as VAL from tEmpe").fetchall()
ROOM=conn.execute("select RoomID as ID,RoomName as VAL from tRoom").fetchall()
wb = xlrd.open_workbook(xls)

readXLS(xls.split('.')[0].upper())
conn.commit()
conn.close()
